package xupt.se.util;


import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;



public class MyDBUtil {
	private final String dbConnFile = "resource/database/myjdbc.properties";
	private Connection conn=null;
	private String dbDriver;	//定义驱动  
    private String dbURL;		//定义URL  
    private String userName;	//定义用户名  
    private String password;	//定义密码	
    
    /**
     * 从配置文件取数据库链接参数  
     */
    private void loadConnProperties(){  
        Properties props = new Properties();  
        try {  
            props.load(new FileInputStream(dbConnFile));//根据配置文件路径Conf加载配置文件  
        } catch (FileNotFoundException e) {  
            e.printStackTrace();  
        } catch (IOException e) {  
            e.printStackTrace();  
        }  
        this.dbDriver = props.getProperty("driver");//从配置文件中取得相应的参数并设置类变量  
        this.dbURL = props.getProperty("url");  
        this.userName = props.getProperty("username");  
        this.password = props.getProperty("password");  
     
    }
    /**
     * 链接数据库
     * @return
     */
	public Connection openConnection(){
		try {  
			loadConnProperties();
            Class.forName(dbDriver);  
            this.conn = DriverManager.getConnection(dbURL,userName,password);
            System.out.println("数据库连接成功");
            return conn;
        } catch(ClassNotFoundException classnotfoundexception) {  
              classnotfoundexception.printStackTrace();  
            System.err.println("db: " + classnotfoundexception.getMessage());  
        } catch(SQLException sqlexception) {  
            System.err.println("db.getconn(): " + sqlexception.getMessage());  
        }
		return	null;
	}
	
	
	
	
	protected void finalize() throws Exception{
		try {
		if(null!=conn)
			conn.close();
		}catch (SQLException e) {
			e.printStackTrace();
		}
		
     }
	// 释放资源
	public void close(ResultSet rst) throws Exception {
		try {
			Statement stmt = rst.getStatement();
			rst.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	// 释放资源
	public void close(Statement stmt) throws Exception {
		try {
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	// 释放资源
	public void close() throws SQLException, Exception{
		if(null!=conn){
			conn.close();
			conn=null;
		}
	}
	
	/**
	 * 查询，传一个类型String,防sql注入攻击
	 * @param sql		sql语句
	 * @param str		where条件
	 * @return
	 * @throws Exception
	 */
	public ResultSet execQuery(String sql,String str) throws Exception {
		Connection conn=null;
		ResultSet rstSet = null;
		PreparedStatement ps=null;	
		conn=this.openConnection();
		//if(obj.getClass().getSimpleName().equals("String"))
		
		try {
			if (null == conn)
				throw new Exception("Database not connected!");
			ps=conn.prepareStatement(sql);
			
			ps.setString(1, str);
			rstSet=ps.executeQuery();
			/*Statement stmt = conn.createStatement();
			rstSet = stmt.executeQuery(sql);*/
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rstSet;
	}
	/**
	 * 查询，传一个类型int,防sql注入攻击
	 * @param sql		sql语句
	 * @param i			where条件
	 * @return
	 * @throws Exception
	 */
	public ResultSet execQuery(String sql,int i) throws Exception {
		
		ResultSet rstSet = null;
		PreparedStatement ps=null;
		Connection conn=null;
		conn=this.openConnection();
		//if(obj.getClass().getSimpleName().equals("String"))
		
		try {
			if (null == conn)
				throw new Exception("Database not connected!");
			ps=conn.prepareStatement(sql);
			ps.setInt(1, i);
			rstSet=ps.executeQuery();
			/*Statement stmt = conn.createStatement();
			rstSet = stmt.executeQuery(sql);*/
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rstSet;
	}	
	public ResultSet execQuery(String sql,float i) throws Exception
	{
		ResultSet rstSet = null;
		PreparedStatement ps=null;
		Connection conn=null;
		conn=this.openConnection();
		//if(obj.getClass().getSimpleName().equals("String"))
		
		try {
			if (null == conn)
				throw new Exception("Database not connected!");
			ps=conn.prepareStatement(sql);
			ps.setFloat(1, i);
			rstSet=ps.executeQuery();
			/*Statement stmt = conn.createStatement();
			rstSet = stmt.executeQuery(sql);*/
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rstSet;
	}
	/**
	 * 删除 数据，传一个类型String，防sql注入攻击
	 * @param sql		sql语句
	 * @param str		where条件
	 * @return
	 * @throws Exception
	 */
	public boolean execDelete(String sql,String str) throws Exception{
		boolean flag = false;
		PreparedStatement ps=null;
		Connection conn=null;
		conn=this.openConnection();
		
		try {
			if(null==conn)
				throw new Exception("Database not connected!");
			
			ps=conn.prepareStatement(sql);
			ps.setString(1,str);
			flag=ps.execute();
			/*
			Statement stmt = conn.createStatement();
			flag = stmt.execute(sql);*/
			
			ps.close();
					
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	/**
	 * 删除 数据，传一个类型int，防sql注入攻击
	 * @param sql
	 * @param i
	 * @return
	 * @throws Exception
	 */
	public boolean execDelete(String sql,int i) throws Exception{
		boolean flag = false;
		PreparedStatement ps=null;
		Connection conn=null;
		conn=this.openConnection();	
		try {
			if(null==conn)
				throw new Exception("Database not connected!");
			
			ps=conn.prepareStatement(sql);
			ps.setInt(1, i);
			flag=ps.execute();
			/*
			Statement stmt = conn.createStatement();
			flag = stmt.execute(sql);*/
			
			ps.close();
					
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	/**
	 * 普通查询，直接执行sql语句，可能被sql注入攻击
	 * 
	 * 可以这样输入实现免帐号登录：

		用户名： ‘or 1 = 1 –

		密 码：
	 * SELECT * FROM user_table WHERE username=

		'’or 1 = 1 -- and password='’
		
		分析SQL语句：
		
		条件后面username=”or 1=1 用户名等于 ” 或1=1 那么这个条件一定会成功；
		
		然后后面加两个-，这意味着注释，它将后面的语句注释，让他们不起作用，这样语句永远都能正确执行，
		用户轻易骗过系统，获取合法身份。
	 * @param sql		sql语句
	 * @return
	 */
	public ResultSet execAllQuery(String sql)
	{
		Connection conn=null;
		ResultSet resultSet=null;
		PreparedStatement ps=null;
		conn=this.openConnection();
		try {
			if (null == conn)
				throw new Exception("Database not connected!");
			ps=conn.prepareStatement(sql);
			resultSet=ps.executeQuery();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return resultSet;
	}
	// 插入一条新纪录，并获取标识列的值
	public ResultSet getInsertObjectIDs(String insertSql) throws Exception{
		ResultSet rst = null;
		try {
			if(null==conn)
				throw new Exception("Database not connected!");
			
			Statement stmt = conn.createStatement();
			
			stmt.executeUpdate(insertSql, Statement.RETURN_GENERATED_KEYS);
			rst = stmt.getGeneratedKeys();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rst;
	}
		
	public boolean execDeleteAll(String sql) throws Exception
	{
		boolean flag = false;		
		Connection conn=null;
		PreparedStatement ps=null;
		conn=this.openConnection();
		try {
			if (null == conn)
				throw new Exception("Database not connected!");
			ps=conn.prepareStatement(sql);
			flag=ps.execute();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return flag;
	}

	public ResultSet execQuery(String sql,int i,int j,String t) throws Exception
	{
		Connection conn=null;
		ResultSet rstSet = null;
		PreparedStatement ps=null;	
		conn=this.openConnection();
		//if(obj.getClass().getSimpleName().equals("String"))
		
		try {
			if (null == conn)
				throw new Exception("Database not connected!");
			ps=conn.prepareStatement(sql);
			ps.setInt(1, i);
			ps.setInt(2, j);
			ps.setString(3, t);
			rstSet=ps.executeQuery();
			/*Statement stmt = conn.createStatement();
			rstSet = stmt.executeQuery(sql);*/
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rstSet;
	}
	public ResultSet execQuerySeat(String sql, int studio_id, int row, int col) throws Exception {
		ResultSet rstSet = null;
		PreparedStatement ps=null;
		Connection conn=null;
		conn=this.openConnection();
		//if(obj.getClass().getSimpleName().equals("String"))
		
		try {
			if (null == conn)
				throw new Exception("Database not connected!");
			ps=conn.prepareStatement(sql);
			ps.setInt(1, studio_id);
			ps.setInt(2, row);
			ps.setInt(3, col);
			rstSet=ps.executeQuery();
			/*Statement stmt = conn.createStatement();
			rstSet = stmt.executeQuery(sql);*/
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rstSet;
	}
}
